/*
	This Procedure returns vendor wise yield for a process.
	EXEC SP_YIELD_REPORT_BY_VENDOR 'ASSEMBLY','0'
*/
CREATE PROC sp_yield_report_by_vendor

@PROCESS_TYPE	VARCHAR(20),
@DEFAULT_PROMPT	VARCHAR(20)

AS
BEGIN
	DECLARE @RESULT_TABLE TABLE (   PART_NUMBER 		VARCHAR(50), 
					PROCESSSTEP_ID 		BIGINT,
					LOT_NUMBER 		VARCHAR(50),
					DATE_IN 		DATETIME,
					DATE_OUT		DATETIME,
					QTY_IN			INT,
					QTY_OUT			INT,
					GROSS_DIE		INT,
					YIELD			FLOAT,
					LOCATION		VARCHAR(50),
					PROCESS			VARCHAR(20),
					PROCESS_TYPE_ID 	INT,
					FAB_LOT_NUMBER		VARCHAR(50))

	DECLARE @PROCESSSTEP_ID INT,@LOT_NUMBER VARCHAR(50),@DATE_IN DATETIME,@DATE_OUT DATETIME,@LOCATION VARCHAR(50),@PROCESS VARCHAR(50),@PROCESS_TYPE_ID INT 
	DECLARE @COMMITED_CYCLE_TIME INT,@PROJECTED_CYCLE_TIME INT,@ACTUAL_CYCLE_TIME INT, @EXPECTED_DATE_OUT DATETIME, @LOT_ID INT
	DECLARE @BASEPRODUCT_ID INT,@BASEPRODUCT_NAME VARCHAR(50)
	
	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	DECLARE @CYCLETIME_CURSOR CURSOR

	INSERT INTO @RESULT_TABLE ( PART_NUMBER,PROCESSSTEP_ID,LOT_NUMBER ,DATE_IN ,DATE_OUT,QTY_IN,QTY_OUT,GROSS_DIE,YIELD,LOCATION,PROCESS,PROCESS_TYPE_ID,FAB_LOT_NUMBER) 
	SELECT FN.PART_NUMBER, FN.PROCESSSTEP_ID, FN.LOT_NUMBER, FN.DATE_IN, FN.DATE_OUT, FN.QTY_IN, FN.QTY_OUT, FN.GROSS_DIE, FN.YIELD, FN.LOCATION, FN.PROCESS, FN.PROCESS_TYPE_ID, FN.FAB_LOT_NUMBER
	FROM FN_MATRIX_YIELD_REPORT ( 0, @PROCESS_TYPE , '0', '0', '0') FN
		
			
	IF ( @PROCESS_TYPE = 'SORT' )
	BEGIN
		SELECT SUM(QTY_IN * GROSS_DIE) AS QTY_IN ,SUM(QTY_OUT) AS QTY_OUT , SUM(QTY_OUT)* 100 /SUM(QTY_IN * GROSS_DIE) AS YIELD, LOCATION 
		FROM @RESULT_TABLE GROUP BY LOCATION
	END
	ELSE
	BEGIN
		SELECT SUM(QTY_IN) AS QTY_IN ,SUM(QTY_OUT) AS QTY_OUT , SUM(QTY_OUT)* 100 /SUM(QTY_IN) AS YIELD, LOCATION 
		FROM @RESULT_TABLE GROUP BY LOCATION
	END
	
END
